/* Copyright (c) 2009 - 2010 98Labs Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.sbal89.map.util;

import static com.sbal89.map.util.BaseUtil.ACCOUNTS;
//import static com.sbal89.map.util.BaseUtil.MAPLIST;
import static com.sbal89.map.util.BaseUtil.SI_NAME;
import static com.sbal89.map.util.BaseUtil.U_NAME;
import static com.sbal89.map.util.BaseUtil.U_PASS;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import com.google.gdata.client.batch.BatchInterruptedException;
import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.RecordQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.Link;
import com.google.gdata.data.PlainTextConstruct;
import com.google.gdata.data.batch.BatchOperationType;
import com.google.gdata.data.batch.BatchStatus;
import com.google.gdata.data.batch.BatchUtils;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.Column;
import com.google.gdata.data.spreadsheet.Data;
import com.google.gdata.data.spreadsheet.Field;
import com.google.gdata.data.spreadsheet.Header;
import com.google.gdata.data.spreadsheet.RecordEntry;
import com.google.gdata.data.spreadsheet.RecordFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.TableEntry;
import com.google.gdata.data.spreadsheet.Worksheet;
import com.google.gdata.util.ServiceException;
import com.sbal89.map.model.Document;
import com.sbal89.map.model.User;

/**
 * This is a utility class or helper for Google's
 * Spreadsheets Data API functionalities.
 *  
 */
public class SDAUtil {

	private static final String SPREADSHEET_FEED_URL = "http://spreadsheets.google.com/feeds/spreadsheets/private/full/";
	
	/**
	 * Used for testing purposes only.
	 * 
	 * @param args
	 * @throws ServiceException 
	 * @throws IOException 
	 * @throws MalformedURLException 
	 * @throws IOException
	 * @throws ServiceException
	 */
    public static void main(String[] args) throws MalformedURLException, IOException, ServiceException {
    	SpreadsheetService myService = new SpreadsheetService(SI_NAME);
		myService.setUserCredentials(U_NAME, U_PASS);
		testDisplayCells(myService);
    }

    public static void testDisplayCells(SpreadsheetService service) throws IOException, ServiceException {
    	int minRow = 1;
    	int maxRow = 81;
    	int minCol = 1;
    	int maxCol = 8;
    	String key = "txmz6TLGWPmXS8MoQSwBBDw";
    	String row = "";
    	int i = 1;
    	for (CellEntry cell : getCells(service, key, minRow, maxRow, minCol, maxCol)) {
    		if (row.length()>0) {
    			row += " * ";
    		}
    		row += cell.getCell().getValue();
    		if ((i%maxCol)==0) {
    			System.out.println(row);
    			row = "";
    		}
    		i++;
    	}
    }
    
	public static List<CellEntry> getCells(SpreadsheetService service,
			String key, int minRow, int maxRow, int minCol, int maxCol)
			throws IOException, ServiceException {
		URL spreasheetURI = new URL(SPREADSHEET_FEED_URL + key);
		SpreadsheetEntry spreadsheetEntry = service.getEntry(spreasheetURI,
				SpreadsheetEntry.class);
		URL cellFeedUrl = spreadsheetEntry.getWorksheets().get(0)
				.getCellFeedUrl();
		CellQuery query = new CellQuery(cellFeedUrl);
		query.setMinimumRow(minRow);
		query.setMaximumRow(maxRow);
		query.setMinimumCol(minCol);
		query.setMaximumCol(maxCol);
		CellFeed feed = service.query(query, CellFeed.class);
		return feed.getEntries();
	}
    
	public static void updateSpreadsheet(SpreadsheetService service,
			String key, HashMap<String, String> newData) throws IOException, ServiceException {
		URL spreasheetURI = new URL(SPREADSHEET_FEED_URL + key);
		SpreadsheetEntry spreadsheetEntry = service.getEntry(spreasheetURI,
				SpreadsheetEntry.class);
		URL cellFeedUrl = spreadsheetEntry.getWorksheets().get(0)
				.getCellFeedUrl();
		batchUpdateCells(service, cellFeedUrl, newData);
	}
    
	public static void testGetSummary(SpreadsheetService service) throws MalformedURLException, IOException, ServiceException {
		SpreadsheetEntry spreadsheetEntry = getSpreadsheetsByUserid(service, "abcm").get(0);
		System.out.println(spreadsheetEntry.getHtmlLink().getHref());
	}
	public static String saveSpreadsheet(SpreadsheetService service,
			String target, HashMap<String, String> newData) throws MalformedURLException,
			IOException, ServiceException {
		SpreadsheetEntry spreadsheetEntry = getSpreadsheetByName(service, target);
		URL cellFeedUrl = spreadsheetEntry.getWorksheets().get(0)
				.getCellFeedUrl();
		batchUpdateCells(service, cellFeedUrl, newData);
		return spreadsheetEntry.getKey();
	}
	
	/**
	 * Utility to get a spreadsheet using its filename.
	 * 
	 * @param service spreadsheet service
	 * @param target filename of the spreadsheet
	 * @return
	 * @throws MalformedURLException
	 * @throws IOException
	 * @throws ServiceException
	 */
	private static SpreadsheetEntry getSpreadsheetByName(
			SpreadsheetService service, String target)
			throws MalformedURLException, IOException, ServiceException {
		SpreadsheetFeed resultFeed = service.getFeed(
				getExactTitleQuery(target), SpreadsheetFeed.class);
		SpreadsheetEntry spreadsheetEntry = resultFeed.getEntries().get(0);
		return spreadsheetEntry;
	}
	
	public static List<SpreadsheetEntry> getSpreadsheetsByUserid(
			SpreadsheetService service, String userid)
			throws MalformedURLException, IOException, ServiceException {
		List<SpreadsheetEntry> list = new ArrayList<SpreadsheetEntry>();
		// we added the . on userid to ensure we only match userids
		// userid.type:mapname
		SpreadsheetFeed resultFeed = service.getFeed(getTitleQuery("U"+userid+"."),
				SpreadsheetFeed.class);
		list = resultFeed.getEntries();
		return list;
	}

//	public static void updateDoc(SpreadsheetService service, String resid, String key) throws MalformedURLException, IOException, ServiceException {
//		URL recordsFeedUrl = getUserRecordFeedUrl(service,MAPLIST);
//	    RecordQuery query = new RecordQuery(recordsFeedUrl);
//	    query.setSpreadsheetQuery("RESOURCEID=\"" + resid+"\"");
//	    RecordFeed feed = service.query(query, RecordFeed.class);
//	    RecordEntry recordEntry = feed.getEntries().get(0);
//	    List<Field> fields = recordEntry.getFields();
//	    for (Field field : fields) {
//	      if (field.getName().equals("KEY")) {
//	        field.setValue(key);
//	        break;
//	      }
//	    }
//	    recordEntry.update();
//	}
	
//	public static void deleteDoc(SpreadsheetService service, String resid) throws MalformedURLException, IOException, ServiceException {
//		URL recordsFeedUrl = getUserRecordFeedUrl(service,MAPLIST);
//	    RecordQuery query = new RecordQuery(recordsFeedUrl);
//	    query.setSpreadsheetQuery("RESOURCEID=\"" + resid+"\"");
//	    RecordFeed feed = service.query(query, RecordFeed.class);
//	    RecordEntry recordEntry = feed.getEntries().get(0);
//	    List<Field> fields = recordEntry.getFields();
//	    for (Field field : fields) {
//	      if (field.getName().equals("ACTIVE")) {
//	        field.setValue("N");
//	        break;
//	      }
//	    }
//	    recordEntry.update();
//	}
	
	public static String getResourceId(SpreadsheetEntry s) {
		String id = s.getHtmlLink().getHref();
		id = id.substring(39, id.length());
		return id;
	}
	
	public static String getSpreadsheetKey(SpreadsheetService service, String resid) throws IOException, ServiceException {
		String key = null;
		for (SpreadsheetEntry se : getSpreadsheets(service)) {
			if(resid.equals(getResourceId(se))) {
				key = se.getKey();
				break;
			}
		}
		return key;
	}
	
	public static Document getDocByName(SpreadsheetService service, String name, String userid) throws MalformedURLException, IOException, ServiceException {
		SpreadsheetEntry entry = getSpreadsheetByName(service, name);
		return convertToDoc(entry, userid);
	}

	private static Document convertToDoc(SpreadsheetEntry entry, String userid) {
		String fulltitle = entry.getTitle().getPlainText();
		String[] part = fulltitle.split(":");
		Document doc = new Document();
		doc.setUserid(userid);
		doc.setTitle(part[1]);
		doc.setType(part[0].split("\\.")[1]);
		doc.setLink(entry.getHtmlLink().getHref());
//		doc.setResourceId(getResourceId(entry)); //already on setLink
		doc.setKey(entry.getKey());
		return doc;
	}
	
	public static Document getDocByResId(SpreadsheetService service, String resid, String userid) throws MalformedURLException, IOException, ServiceException {
		Document result = new Document();
		for (Document doc : getDocs(service, userid)) {
			if(doc.getResourceId().equals(resid)) {
				result = doc;
				break;
			}
		}
		return result;
	}

	public static ArrayList<Document> getDocs(SpreadsheetService service, String userid) throws IOException, ServiceException {
	    ArrayList<Document> docs = new ArrayList<Document>();
		for (SpreadsheetEntry entry : SDAUtil.getSpreadsheetsByUserid(service, userid)) {
			Document doc = convertToDoc(entry, userid);
			docs.add(doc);
		}
		return docs;
	}
	
	public static Integer getUserCount(SpreadsheetService service) throws IOException, ServiceException {
		HashMap<String, User> users = getUsers(service);
		int userCount = (users!= null) ? users.size() : 0;
		return userCount;
	}
	
	public static HashMap<String, User> getUsers(SpreadsheetService service) throws IOException, ServiceException {
		URL recordsFeedUrl = getUserRecordFeedUrl(service, ACCOUNTS);
	    RecordQuery query = new RecordQuery(recordsFeedUrl);
//	    query.setSpreadsheetQuery("USERID=" + user);
//	    System.out.println(query.getUrl().toString());
	    RecordFeed feed = service.query(query, RecordFeed.class);
	    HashMap<String, User> users = new HashMap<String, User>();
		for (RecordEntry entry : feed.getEntries()) {
			User user = new User();
			for (Field field : entry.getFields()) {
//				System.out.println("<field name=" + field.getName() + ">"
//						+ field.getValue() + "</field>");
				String fieldName = field.getName();
				String fieldValue = field.getValue();
				if (fieldValue != null) {
					if ("ID".equals(fieldName)) {
						user.setId(new Integer(fieldValue));
					} else if ("USERNAME".equals(fieldName)) {
						user.setUsername(fieldValue);
					} else if ("EMAIL".equals(fieldName)) {
						user.setEmail(fieldValue);
					} else if ("FULLNAME".equals(fieldName)) {
						user.setFullname(fieldValue);
					} else if ("SIGNUP".equals(fieldName)) {
						user.setSignup(fieldValue);
					}
				}
			}
			users.put(user.getUsername(), user);
		}
		return users;
	}
	
	private static URL getUserRecordFeedUrl(SpreadsheetService service, String name) throws MalformedURLException, IOException, ServiceException {
		FeedURLFactory factory = FeedURLFactory.getDefault();
		URL recordsFeedUrl = factory.getRecordFeedUrl(getSpreadsheetByName(service, name).getKey(), "0");
		return recordsFeedUrl;
	}
	
	/**
	 * Utility to add new user.
	 * 
	 * @param service spreadsheet service
	 * @param user yahoo or google email address;
	 * @throws MalformedURLException
	 * @throws IOException
	 * @throws ServiceException
	 */
	public static void addNewUser(SpreadsheetService service, User user)
			throws MalformedURLException, IOException, ServiceException {
		URL recordsFeedUrl = getUserRecordFeedUrl(service, ACCOUNTS);
		RecordEntry recordEntry = new RecordEntry();
		if (user.getId() != null) {
			recordEntry.addField(new Field(null, "ID", user.getId() + ""));
		}
		if (user.getUsername() != null) {
			recordEntry
					.addField(new Field(null, "USERNAME", user.getUsername()));
		}
		if (user.getEmail() != null) {
			recordEntry.addField(new Field(null, "EMAIL", user.getEmail()));
		}
		if (user.getFullname() != null) {
			recordEntry
					.addField(new Field(null, "FULLNAME", user.getFullname()));
		}
		if (user.getSignup() != null) {
			recordEntry.addField(new Field(null, "SIGNUP", user.getSignup()));
		}
		service.insert(recordsFeedUrl, recordEntry);
	}
	
//	/**
//	 * Utility to add new map.
//	 * 
//	 * @param service spreadsheet service
//	 * @param userid userid
//	 * @param key spreadsheet key
//	 * @param type map type
//	 * 
//	 * @throws MalformedURLException
//	 * @throws IOException
//	 * @throws ServiceException
//	 */
//	public static void addNewUserMap(SpreadsheetService service, String userid, String key, String type)
//			throws MalformedURLException, IOException, ServiceException {
//		URL recordsFeedUrl = getUserRecordFeedUrl(service, MAPLIST);
//		RecordEntry recordEntry = new RecordEntry();
//		if (userid != null) {
//			recordEntry.addField(new Field(null, "USERID", userid));
//		}
//		if (key != null) {
//			recordEntry
//					.addField(new Field(null, "RESOURCEID", key));
//		}
//		if (type != null) {
//			recordEntry.addField(new Field(null, "TYPE", type));
//		}
//		recordEntry.addField(new Field(null, "ACTIVE", "Y"));
//		service.insert(recordsFeedUrl, recordEntry);
//	}
	
//	/**
//	 * Utility to create a table to hold user maps details.
//	 *  
//	 * @param service spreadsheet service
//	 * @param target filename of the spreadsheet to be used.
//	 * @throws ServiceException 
//	 * @throws IOException 
//	 * @throws MalformedURLException 
//	 */
//	public static void createUserMapListTable(SpreadsheetService service) throws MalformedURLException, IOException, ServiceException {
//		FeedURLFactory factory = FeedURLFactory.getDefault();
//		URL tablesFeedUrl = factory.getTableFeedUrl(getSpreadsheetByName(service, MAPLIST).getKey());
//		
//		// Specify a basic table:
//		TableEntry tableEntry = new TableEntry();
//		tableEntry.setTitle(new PlainTextConstruct("User Map List Table"));
//		tableEntry.setWorksheet(new Worksheet("Sheet1"));
//		tableEntry.setHeader(new Header(1));
//
//		// Specify columns in the table, start row, number of rows.
//		Data tableData = tableEntry.getData();
//		if (tableData == null) {
//			tableData = new Data();
//		}
//		tableData.setNumberOfRows(0);
//		// Start row index cannot overlap with header row.
//		tableData.setStartIndex(2);
//		// This table has only one column.
//		tableData.addColumn(new Column("a", "USERID"));
//		tableData.addColumn(new Column("b", "RESOURCEID"));
//		tableData.addColumn(new Column("c", "KEY"));
//		tableData.addColumn(new Column("d", "TYPE"));
//		tableData.addColumn(new Column("e", "ACTIVE"));
//		tableEntry.setData(tableData);
//		service.insert(tablesFeedUrl, tableEntry);		
//	}

	/**
	 * Utility to create a table to hold user accounts.
	 *  
	 * @param service spreadsheet service
	 * @param target filename of the spreadsheet to be used.
	 * @throws ServiceException 
	 * @throws IOException 
	 * @throws MalformedURLException 
	 */
	public static void createUserAccountsTable(SpreadsheetService service) throws MalformedURLException, IOException, ServiceException {
		FeedURLFactory factory = FeedURLFactory.getDefault();
		URL tablesFeedUrl = factory.getTableFeedUrl(getSpreadsheetByName(service, ACCOUNTS).getKey());
		
		// Specify a basic table:
		TableEntry tableEntry = new TableEntry();
		tableEntry.setTitle(new PlainTextConstruct("User Acounts Table"));
		tableEntry.setWorksheet(new Worksheet("Sheet1"));
		tableEntry.setHeader(new Header(1));

		// Specify columns in the table, start row, number of rows.
		Data tableData = tableEntry.getData();
		if (tableData == null) {
			tableData = new Data();
		}
		tableData.setNumberOfRows(0);
		// Start row index cannot overlap with header row.
		tableData.setStartIndex(2);
		// This table has only one column.
		tableData.addColumn(new Column("a", "ID"));
		tableData.addColumn(new Column("b", "USERNAME"));
		tableData.addColumn(new Column("c", "EMAIL"));
		tableData.addColumn(new Column("d", "FULLNAME"));
		tableData.addColumn(new Column("e", "SIGNUP"));
		tableEntry.setData(tableData);
		service.insert(tablesFeedUrl, tableEntry);		
	}
	
//    private static HashMap<String, String> getSampleMap(int num) {
//    	HashMap<String, String> md = new HashMap<String, String>();
//    	if (num == 1) {
//    		md.put("1","MAP00001");
//    		md.put("3","Title for Map #1");
//    		md.put("5","Foo Bar");
//    		md.put("7","This is the first Map.");
//    	} else if (num == 2) {
//    		md.put("1","MAP00002");
//    		md.put("3","Title for Map #2");
//    		md.put("5","Bob Ong");
//    		md.put("7","This is the second Map.");
//    	} else {
//    		md.put("1","map id goes here");
//    		md.put("3","map title goes here");
//    		md.put("5","author name goes here");
//    		md.put("7","map description goes here");
//    	}
//    	return md;
//    }
    
	public static Integer getMapCount(SpreadsheetService service) throws IOException, ServiceException {
			List<SpreadsheetEntry> sprSheets = getSpreadsheets(service);
			int sprSheetCount = (sprSheets!=null) ? sprSheets.size() : 1;
			return sprSheetCount - 1;
	}
	
	public static List<SpreadsheetEntry> getSpreadsheets(SpreadsheetService service)
			throws IOException, ServiceException {
		URL metafeedUrl = new URL(SPREADSHEET_FEED_URL);
		SpreadsheetFeed feed = service.getFeed(metafeedUrl,
				SpreadsheetFeed.class);
		return feed.getEntries();
	}

	private static SpreadsheetQuery getExactTitleQuery(String target)
			throws MalformedURLException {
		URL metafeedUrl = new URL(SPREADSHEET_FEED_URL);
		SpreadsheetQuery sq = new SpreadsheetQuery(metafeedUrl);
		sq.setTitleExact(true);
		sq.setTitleQuery(target);
		return sq;
	}

	private static SpreadsheetQuery getTitleQuery(String target)
			throws MalformedURLException {
		URL metafeedUrl = new URL(SPREADSHEET_FEED_URL);
		SpreadsheetQuery sq = new SpreadsheetQuery(metafeedUrl);
		sq.setTitleQuery(target);
		return sq;
	}

	public static void batchUpdateCells(SpreadsheetService service,
			URL cellFeedUrl, HashMap<String, String> newData) throws BatchInterruptedException,
			MalformedURLException, IOException, ServiceException {
		
		// Workaround for the (If-Match or If-None-Match header required) bug
		service.setProtocolVersion(SpreadsheetService.Versions.V1);
		
		CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);
		List<CellEntry> cellEntries = cellFeed.getEntries();
		
		// Add the operations to a batch feed.
		CellFeed batchFeed = new CellFeed();
		
		Iterator<String> itr = newData.keySet().iterator();
		while (itr.hasNext()) {
			String key = itr.next();
			int index = Integer.parseInt(key);
			batchFeed.getEntries().add(
					createUpdateOperation(cellEntries, index, newData.get(key)));

		}
		
		// Submit the batch request.
		Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
		CellFeed batchResultFeed = service.batch(new URL(batchLink.getHref()),
				batchFeed);

		// Make sure all the operations were successful.
		for (CellEntry entry : batchResultFeed.getEntries()) {
			String batchId = BatchUtils.getBatchId(entry);
			if (!BatchUtils.isSuccess(entry)) {
				BatchStatus status = BatchUtils.getBatchStatus(entry);
				System.err.println("Failed entry");
				System.err.println("\t" + batchId + " failed ("
						+ status.getReason() + ") " + status.getContent());
			}
		}
	}

	/**
	 * Returns a CellEntry with batch id and operation type that will tell the
	 * server to update the specified cell with the given value. The entry is
	 * fetched from the server in order to get the current edit link (for
	 * optimistic concurrency).
	 * 
	 * @param cellEntries
	 * @param index
	 * @param value
	 * @return
	 * @throws ServiceException
	 * @throws IOException
	 */
	private static CellEntry createUpdateOperation(List<CellEntry> cellEntries,
			int index, String value) throws ServiceException, IOException {
		String batchId = "c" + index;
		CellEntry entry = cellEntries.get(index);
		entry.changeInputValueLocal(value);
		BatchUtils.setBatchId(entry, batchId);
		BatchUtils.setBatchOperationType(entry, BatchOperationType.UPDATE);
		return entry;
	}
	
}
